<?php
/*
Plugin Name: Synergy Message System
Plugin URI: http://www.synergy.com/
Description: Message system wordpress plugin for Synergy Resource management system.
Version: 1.0
Author: Weranga Kaluarachchi
Author URI: http://www.synergy.com
License: GPL
*/


// import javascript file into syn_message_system plugin
wp_enqueue_script('syn_message_system_script', '/wp-content/plugins/syn_message_system/syn_message_system.js');


/**
 * Main method of the synergy message system plugin
 */
function widget_syn_message_system() {
	echo $before_widget; 
	global $current_user;
	global $wpdb;
	if(isset($_POST[btnSynMsgSend]) && ($_POST[btnSynMsgSend] == "send")) {	
		insert_message();		
	}
	else if(isset($_POST[btnSynMsgDelete]) && (strpos('$_POST[btnSynMsgDelete]', 'Delete'))) {
		$msgID = substr($_POST[btnSynMsgDelete], 6);
		delete_message_from_inbox($msgID, $current_user->ID);
	}
	else if(isset($_POST[btnSynMsgRead]) && (strpos('$_POST[btnSynMsgRead]', 'Read'))) {		
		$msgID = substr($_POST[btnSynMsgRead], 4);
		mark_read_message($msgID, $current_user->ID);
	}
	else if(isset($_POST[btnSynSentMsgDelete]) || (strpos('$_POST[btnSynSentMsgDelete]', 'DeleteSent'))) {
		$msgID = substr($_POST[btnSynSentMsgDelete], 10);
		delete_message_from_sentItem($msgID);
	}		
	
	?>
	
	<form action="" method="post">
	<div>
	<h4>Messages</h4>
		
	<?php 
	$msgRecTable = $wpdb->prefix."messageRec";
	$msgTable = $wpdb->prefix."message";
	$projTable = $wpdb->prefix."projects";
	$noofNewMsgs = $wpdb->get_var("SELECT COUNT($msgRecTable.MESSAGE_ID) FROM $msgRecTable, $msgTable WHERE  $msgRecTable.MESSAGE_ID =  $msgTable.MESSAGE_ID AND $msgRecTable.IS_DELETED = '0' AND $msgTable.IS_DELETED = '0' AND $msgRecTable.IS_READ = '0' AND $msgRecTable.RECIPIENT_ID = $current_user->ID");
	if(!isset($noofNewMsgs) || $noofNewMsgs ==0) {
		$noofNewMsgs = "";
	}
	else {
		$noofNewMsgs = " (".$noofNewMsgs.") ";
	}
	if(!isset($noofSentMsgs) || $noofSentMsgs ==0) {
		$noofSentMsgs = "";
	}
	else {
		$noofSentMsgs = " (".$noofSentMsgs.") ";
	}
	
	echo '<div class="synleft"><div class="inboxTitleDiv"><span class="inboxTitle">Inbox<b>'.$noofNewMsgs.'</b></span><span onclick="toggleMessage(\'dvInbox\'
	 ,this);" style="cursor:pointer"> [hide]</span></div>';		
	echo "<div id='dvInbox' style='display:inline;'>\n"; 
	foreach (get_new_messages_by_contributor($current_user->ID) as $myInboxMsg) {
		echo "<div class='message'>";
		$displayMsg = $myInboxMsg->MESSAGE_TEXT;
		if($myInboxMsg->IS_READ == "0"){
			//echo "<p><a onclick=toggleOneMessage('message" . $myInboxMsg->MESSAGE_ID . "');  href='#'><b>" . $displayMsg . "</b></a>";
			echo "<b>" . $displayMsg . "</b>";
		}
		else {
			//echo "<p><a onclick=toggleOneMessage('message" . $myInboxMsg->MESSAGE_ID . "'); href='#'>" . $displayMsg . "</a>";
			echo $displayMsg;
		}
		echo "<div id='msgFromProject' class='msgFromProject'>".get_project_by_ID($myInboxMsg->PROJECT_ID)."</div>";		
		$timeStamp = convert_message_datetime($myInboxMsg->MESSAGE_DATE);
		echo "<div class='msgSender' id='msgSender'>". $myInboxMsg->first_name." ".$myInboxMsg->last_name." ".date("F j, Y g:ia", $timeStamp)."</div>";
	    echo "<button type='submit' class='link' name='btnSynMsgDelete' id='btnSynMsgDelete' value='Delete$myInboxMsg->MESSAGE_ID' onclick='return confirmMessageDelete();'>delete</button>\n";
		if($myInboxMsg->IS_READ == '0') {
			echo "<button type='submit' class='link' name='btnSynMsgRead' id='btnSynMsgRead' value='Read$myInboxMsg->MESSAGE_ID'>mark read</button>\n";	
		}
		echo "<div id='message" . $myInboxMsg->MESSAGE_ID . "' style='display:none;'>\n"; 
		//echo "<div id='msgDate'> on ". date("F j, Y g:ia",$myInboxMsg->MESSAGE_DATE)."</div>";		
		//echo "<div id='fullMessage'><b>Message:</b> ". $myInboxMsg->MESSAGE_TEXT."</div>\n";
				
		echo "</div></div>";
	}	
	
	echo "</div>";
		
	echo '</div><div class="synright"><div class="inboxTitleDiv"><span class="inboxTitle">Sent Items<b>'.$noofSentMsgs.' </b></span><span onclick="toggleMessage(\'dvSentItem\'
	 , this);" style="cursor:pointer"> [show]</span></div>' . "\n";		
	echo "<div id='dvSentItem' style='display:none;'>\n"; 
	
	
	foreach (get_sent_messages_by_contributor($current_user->ID) as $mySentMsg) {
		$sentTimeStamp = convert_message_datetime($mySentMsg->MESSAGE_DATE);
		$displaySentMsg = $mySentMsg->MESSAGE_TEXT;		
		echo "<div class='message' id='sentMessage'" . $mySentMsg->MESSAGE_ID . "' >$displaySentMsg\n"; 
		echo "<div class='msgProject' id='msgProject'> to ". $mySentMsg->PROJ_NAME."";
		echo ", ". date("F j, Y g:ia", $sentTimeStamp)."</div>\n";		
		echo "<button type='submit' class='link' name='btnSynSentMsgDelete' id='btnSynSentMsgDelete' value='DeleteSent$mySentMsg->MESSAGE_ID' onclick='return confirmMessageDelete();'>delete</button>&nbsp;&nbsp;&nbsp;";
		echo "</div>";		
	}
	echo "</div></p>";	
		
	
	?>
	<div class="messageForm">
	<table>
	<tr><td colspan='2'><span class="messageFormTitle">New Text Message</span></td></tr>
	<tr>
	<td align="left"><label>Project:</label></td>
	<td><select name="ldbProjects" id="ldbProjects" size="1" style="width:93%">	
	<?php 
	$projectFlag = false;
	foreach (get_projects_by_contributor($current_user->ID) as $myrow) {
		if ($myrow->PROJ_ID == syn_get_current_project()) {
			echo "<option value=$myrow->PROJ_ID selected='selected'>$myrow->PROJ_NAME</option>";			
		}
		else {
			echo "<option value=$myrow->PROJ_ID>$myrow->PROJ_NAME</option>";
		}
		$projectFlag = true;
	}	
	if(!$projectFlag) {
		echo "<option value=-1>No projects</option>";
	}
	
	?>
	</select>
	
	</td>
	</tr>
	<tr>
	<td align="left"><label>Text:</label></td><td></td>
	</tr><tr>
	<td colspan="2"><textarea rows="4" cols="30" id="txaMessage" name="txaMessage" onkeydown="textMessageLimit(this, 140);"></textarea></td></tr>
	<tr><td colspan="2" align="right"><i>maximum length 140 characters.</i></td></tr>
	<tr><td></td><td align="right"><button type="submit" id="btnSynMsgSend" name="btnSynMsgSend" value='send' onclick='return validateMessage();'>Send</button></td></tr>
	</table>
	</div>
	</div>
	</form>
	</div>
	<?php
	echo $after_widget;	
}


/**
 * Insert message and recipients to the database
 */
function insert_message() {
	global $wpdb;
	global $current_user;
	$msgTable = $wpdb->prefix."message";
	$msgRecTable = $wpdb->prefix."messageRec";
	$projContrTable = $wpdb->prefix."projectcontributors";
	$messageAfterClean = cleanMessageInput($_POST[txaMessage]);
	$insertMsgSchema = "INSERT INTO $msgTable(SENDER_ID, PROJECT_ID, MESSAGE_TEXT, IS_DELETED)
    VALUES($current_user->ID, $_POST[ldbProjects],'$messageAfterClean', '0')";
	$newMsgID = 0;
	$wpdb->query($insertMsgSchema);
	$newMsgID = $wpdb->get_var("SELECT MAX(MESSAGE_ID) FROM $msgTable");
	$getContIDRows = $wpdb->get_results("SELECT CONTRIBUTOR_ID FROM $projContrTable WHERE PROJ_ID=$_POST[ldbProjects]");
	foreach (get_contributors_by_project($_POST[ldbProjects]) as $contrRow) {
		if($contrRow->CONTRIBUTOR_ID != $current_user->ID){
			$insertMsgRecSchema = "INSERT INTO $msgRecTable(MESSAGE_ID, RECIPIENT_ID, IS_READ, IS_DELETED)
    		VALUES($newMsgID, $contrRow->CONTRIBUTOR_ID, '0', '0')";
			$wpdb->query($insertMsgRecSchema);
		}					
	}		
}


/**
 * Convert date time to timestamp
 * @param	string  datetime
 * @return	string timestamp
 */
function convert_message_datetime($str) {
	list($date, $time) = explode(' ', $str);
	list($year, $month, $day) = explode('-', $date);
	list($hour, $minute, $second) = explode(':', $time);
	
	$timestamp = mktime($hour, $minute, $second, $month, $day, $year);
	
	return $timestamp;
}


/**
 * Get contributors of a particular project
 * @param	int  projectID of the project
 */
function get_contributors_by_project($projID) {
	global $wpdb;
	$projContrTable = $wpdb->prefix."projectcontributors";
	$getContIDRows = $wpdb->get_results("SELECT DISTINCT CONTRIBUTOR_ID FROM $projContrTable WHERE PROJ_ID=$projID");
	return $getContIDRows;
}


/**
 * Get all new messages for a partcular user 
 * @return	resultset  result set of new messages
 */
function get_new_messages_by_contributor($contributorID) {
	global $wpdb;
	$msgTable = $wpdb->prefix."message";
	$msgRecTable = $wpdb->prefix."messageRec";
	$userTable = $wpdb->prefix."users";
	$usermetaTable = $wpdb->prefix."usermeta";
	$getMessageQuery = "SELECT DISTINCT x.MESSAGE_ID, x.MESSAGE_DATE, x.SENDER_ID, x.MESSAGE_TEXT, x.PROJECT_ID, y.IS_READ, a.meta_value AS first_name, b.meta_value AS last_name
						FROM $msgTable AS x, $msgRecTable AS y, $userTable AS z, $usermetaTable AS a, $usermetaTable AS b WHERE x.MESSAGE_ID = y.MESSAGE_ID AND x.SENDER_ID <>$contributorID AND y.IS_DELETED = '0' AND y.RECIPIENT_ID =$contributorID AND x.SENDER_ID = z.ID
						AND (a.user_id = z.id AND b.user_id = z.id AND b.user_id = z.id AND a.meta_key = 'first_name' AND b.meta_key = 'last_name' ) ORDER BY y.IS_READ ASC , x.MESSAGE_DATE DESC";
	$myMsgRows = $wpdb->get_results($getMessageQuery);		
	return $myMsgRows;	
}


/**
 * Get all sent messages for a partcular user 
 * @return	resultset  result set of sent messages
 */
function get_sent_messages_by_contributor($contributorID) {
	global $wpdb;
	$msgTable = $wpdb->prefix."message";
	$projectTable = $wpdb->prefix."projects";
	$myMsgRows = $wpdb->get_results("SELECT a.MESSAGE_ID, a.MESSAGE_TEXT, a.MESSAGE_DATE, b.PROJ_NAME
	FROM $projectTable as b, $msgTable as a WHERE a.PROJECT_ID = b.PROJ_ID AND a.IS_DELETED = '0' AND a.SENDER_ID = $contributorID ORDER BY a.MESSAGE_DATE DESC");	
	return $myMsgRows;	
}


/**
 * Delete a selected message from contributor's inbox
 * @param	int	ID of the message
 * @param	int	ID of the recipient
 */
function delete_message_from_inbox($msgID, $recipientID) {
	global $wpdb;
	$table = $wpdb->prefix."messageRec";	
	$deleteSchema = "UPDATE $table SET IS_DELETED='1' WHERE MESSAGE_ID = $msgID AND RECIPIENT_ID = $recipientID";
	$wpdb->query($deleteSchema);
}

/**
 * Delete a selected message from contributor's sent item
 * @param	int	ID of the message
 */
function delete_message_from_sentItem($msgID) {
	global $wpdb;
	$table = $wpdb->prefix."message";
	$messageRecTable = $wpdb->prefix."messageRec";
	$deleteSchema = "UPDATE $table SET IS_DELETED='1' WHERE MESSAGE_ID = $msgID";
	$wpdb->query($deleteSchema);
	$deleteMsgRecSchema = "UPDATE $messageRecTable SET IS_DELETED='1' WHERE MESSAGE_ID = $msgID";
	$wpdb->query($deleteMsgRecSchema);	
}

/**
 * Mark read a selected message from contributor's inbox
 * @param	int	ID of the message
 */
function mark_read_message($msgID, $recipientID) {
	global $wpdb;
	$table = $wpdb->prefix."messageRec";
	$readSchema = "UPDATE $table SET IS_READ='1' WHERE MESSAGE_ID = $msgID AND RECIPIENT_ID = $recipientID";
	$wpdb->query($readSchema);
}


/**
 * Sanitize Database Inputs
 * @param	string	input applying sanitize
 */
function cleanMessageInput($input) {

  $search = array(
    '@<script[^>]*?>.*?</script>@si',   // Strip out javascript
    '@<[\/\!]*?[^<>]*?>@si',            // Strip out HTML tags
    '@<style[^>]*?>.*?</style>@siU',    // Strip style tags properly
    '@<![\s\S]*?--[ \t\n\r]*>@'         // Strip multi-line comments
  );

    $output = preg_replace($search, '', $input);
    return $output;
 }


/**
 * Get projects of the selected user
 * @return	resultset set of projects
 */
function get_projects_by_contributor($contributorID) {
	global $wpdb;
	$projContTable = $wpdb->prefix."projectcontributors";
	$projTable = $wpdb->prefix."projects";
	$myrows = $wpdb->get_results("SELECT DISTINCT $projTable.PROJ_ID, $projTable.PROJ_NAME
	FROM $projTable, $projContTable WHERE $projContTable.PROJ_ID = $projTable.PROJ_ID
	AND $projContTable.CONTRIBUTOR_ID = $contributorID ORDER BY $projTable.PROJ_NAME ASC");	
	return $myrows;	
}


/**
 * Install the syn_message_system plugin to the system. It will create 
 * all the tables in the wordpress database.
 */
function syn_message_install() {
	global $wpdb;
	global $current_user;
	$msgTable = $wpdb->prefix."message";
	$msgRecTable = $wpdb->prefix."messageRec";
	$contributorTable = $wpdb->prefix."contributors";
	$wpdb->query("DROP TABLE IF EXISTS $msgTable");
	$wpdb->query("DROP TABLE IF EXISTS $msgRecTable");

	$messageSchema = "CREATE TABLE IF NOT EXISTS $msgTable (
        MESSAGE_ID INT(9) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        SENDER_ID INT(9) NOT NULL,
        PROJECT_ID INT(9) NOT NULL,
        MESSAGE_DATE TIMESTAMP DEFAULT NOW(),
        MESSAGE_TEXT VARCHAR(1000) NOT NULL,      
        IS_DELETED CHAR(1) NOT NULL                      	
    );";
	
	$wpdb->query($messageSchema);
	
	$messageRecSchema = "CREATE TABLE IF NOT EXISTS $msgRecTable (
        MESSAGE_ID INT(9) NOT NULL,
        RECIPIENT_ID INT(9) NOT NULL,
        IS_READ CHAR(1) NOT NULL,
        IS_DELETED CHAR(1) NOT NULL, 
        FOREIGN KEY (MESSAGE_ID) REFERENCES $msgTable (MESSAGE_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    	FOREIGN KEY (RECIPIENT_ID) REFERENCES $contributorTable (CONT_ID) ON UPDATE CASCADE ON DELETE CASCADE
                        	
    );";

	$wpdb->query($messageRecSchema);
}

// register syn_message_system widget  
register_sidebar_widget('Synergy Message System', 'widget_syn_message_system');

// register plugin and install it to the wordpress
add_action('activate_syn_message_system/syn_message_main.php', 'syn_message_install');
?>